﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using StuManDAL.Common;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace StuManDAL
{
    public class ScoreService
    {
        public DataSet GetStuScoByClass(int classId, out string info)
        {
            string sql = $"select StuId,StuName,ClassName,SubjectName,Value from StudentScore " +
                $"inner join Student on Student.StuId=StudentScore.ScorStuId " +
                $"inner join StudentSubject on StudentSubject.SubjectId=StudentScore.ScorSubjectId " +
                $"inner join StudentClass on StudentClass.ClassId=Student.StuClassId " +
                $"where ClassId={classId}";
            try
            {
                info = string.Empty;
                return SQLHelper.Instance.GetDataSet(sql);
            }
            catch (SqlException ex)
            {
                info = $"数据库操作出现异常,具体信息为:{ex.Message}";
                return null;
            }
            catch (Exception ex)
            {
                info = ex.Message;
                return null;
            }
        }
        public DataSet GetStuScoBySub(int subjectId, out string info)
        {
            string sql = $"select StuId,StuName,ClassName,SubjectName,Value from StudentScore " +
                              $"inner join Student on Student.StuId=StudentScore.ScorStuId " +
                              $"inner join StudentSubject on StudentSubject.SubjectId=StudentScore.ScorSubjectId " +
                              $"inner join StudentClass on StudentClass.ClassId=Student.StuClassId " +
                              $"where SubjectId={subjectId}";
            try
            {
                info = string.Empty;
                return SQLHelper.Instance.GetDataSet(sql);
            }
            catch (SqlException ex)
            {
                info = $"数据库操作出现异常,具体信息为:{ex.Message}";
                return null;
            }
            catch (Exception ex)
            {
                info = ex.Message;
                return null;
            }
        }
        public DataSet GetScoreAndOrderBy(int subjectId,double score,out string info)
        {
            string sql = $"select StuId,StuName,ClassName,SubjectName,Value from StudentScore " +
                              $"inner join Student on Student.StuId=StudentScore.ScorStuId " +
                              $"inner join StudentSubject on StudentSubject.SubjectId=StudentScore.ScorSubjectId " +
                              $"inner join StudentClass on StudentClass.ClassId=Student.StuClassId " +
                              $"where SubjectId={subjectId} and Value>{score} order by Value desc";
            try
            {
                info = string.Empty;
                return SQLHelper.Instance.GetDataSet(sql);
            }
            catch (SqlException ex)
            {
                info = $"数据库操作出现异常,具体信息为:{ex.Message}";
                return null;
            }
            catch (Exception ex)
            {
                info = ex.Message;
                return null;
            }
        }
        public DataSet GetAllScore(out string info)
        {
            string sql = "select StuId,StuName,ClassName,SubjectName,Value from StudentScore " +
                              "inner join Student on Student.StuId=StudentScore.ScorStuId " +
                              "inner join StudentSubject on StudentSubject.SubjectId=StudentScore.ScorSubjectId " +
                              "inner join StudentClass on StudentClass.ClassId=Student.StuClassId ";
            try
            {
                info = string.Empty;
                return SQLHelper.Instance.GetDataSet(sql);
            }
            catch (SqlException ex)
            {
                info = $"数据库操作出现异常,具体信息为:{ex.Message}";
                return null;
            }
            catch (Exception ex)
            {
                info = ex.Message;
                return null;
            }
        }
        public DataSet GetAllInfo(out string info)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("select ClassId, ClassName from StudentClass; ");
            sb.Append("select SubjectId,SubjectName from StudentSubject; ");
            sb.Append("select StuId,StuName,ClassName,SubjectName,Value from StudentScore ");
            sb.Append("inner join Student on Student.StuId=StudentScore.ScorStuId ");
            sb.Append("inner join StudentSubject on StudentSubject.SubjectId=StudentScore.ScorSubjectId ");
            sb.Append("inner join StudentClass on StudentClass.ClassId=Student.StuClassId ");
            try
            {
                info = string.Empty;
                return SQLHelper.Instance.GetDataSet(sb.ToString());
            }
            catch (SqlException ex)
            {
                info = $"数据库操作出现异常,具体信息为:{ex.Message}";
                return null;
            }
            catch (Exception ex)
            {
                info = ex.Message;
                return null;
            }

        }

    }
}
